﻿CREATE Trigger [dbo].[tru_Loan_Verification_Checked]
On [dbo].[t_Loan_Verification]
FOR UPDATE
As


IF @@ROWCOUNT = 0 RETURN

IF NOT UPDATE(Checked) RETURN

Declare @LoanID int, @VerID int, @Checked bit,@IsCustom varchar,@Lable varchar(1024),
				@IsOwner bit, @IsParent bit,@IsSmart bit

select @LoanID = i.LoanID,@VerID = i.VerificationID,@IsCustom = i.IsCustom,@Lable = i.Lable,
			 @IsOwner = CASE WHEN i.Owner = dbo.UserName() OR dbo.UserLike('Admin') = 1 THEN 1 ELSE 0 END,
			 @Checked = i.Checked, @IsParent = LV.IsParent,@IsSmart = IsSmart
from inserted i INNER JOIN v_Loan_Verification LV ON i.LoanID = LV.LoanID AND i.VerificationID = LV.ID

IF trigger_nestlevel(@@PROCID) = 1 AND @IsParent = 1 AND @VerID > 0 BEGIN
	RAISERROR('Must not click on parent. Check children one by one instead.',16,1)
	ROLLBACK TRAN
	RETURN
END

IF @Checked = 1
	IF EXISTS(SELECT * FROM t_Loan_Verification WHERE LoanID = @LoanID AND ParentID = @VerID AND Checked = 0) BEGIN
		ROLLBACK TRAN
		RAISERROR('Some children are not checked.',16,1)
		RETURN
	END

-- Propagate Checked Up to Parents
UPDATE t_Loan_Verification
SET Checked = CH.Checked
FROM t_Loan_Verification
INNER JOIN (SELECT MIN(CONVERT(int,LV.Checked)) AS Checked,i.ParentID,i.LoanID
						FROM v_Loan_Verification_01 LV
						INNER JOIN (SELECT i.LoanID,LV.ParentID FROM inserted i
												INNER JOIN v_Loan_Verification_01 LV
												ON i.LoanID = LV.LoanID AND i.VerificationID = LV.ID
												WHERE i.VerificationID > 0 OR i.Checked = 0) i
						ON LV.LoanID = i.LoanID AND LV.ParentID = i.ParentID
						GROUP BY i.LoanID,i.ParentID) CH
ON t_Loan_Verification.LoanID = CH.LoanID AND
	 t_Loan_Verification.VerificationID = CH.ParentID AND
	 t_Loan_Verification.Checked <> CH.Checked
